\documentclass[a4paper,12pt]{book}

\usepackage[utf8]{inputenc}
\usepackage[polish]{babel}
\usepackage{polski}
\usepackage{graphicx}
\usepackage{hyperref}
\hypersetup{colorlinks,citecolor=black,filecolor=black,linkcolor=black,urlcolor=black,pdftex}

\usepackage{verbatim}
\usepackage{array}
\usepackage{longtable}
\usepackage{float}
\usepackage{setspace}

\usepackage[T1]{fontenc}
\usepackage{a4wide}

\setcounter{secnumdepth}{3}
\setcounter{tocdepth}{3}
\usepackage[left=3cm,right=2cm, bottom=2cm, top=2.5cm]{geometry}

\onehalfspacing
\usepackage[numbers]{natbib}

\makeatletter
\makeatother

\author{Piotr Wilk, Piotr Zegar}
\title{Projekt SQL: }
\date{2009-12-27}

\begin{document}
\maketitle
\tableofcontents
\pagebreak

\section{Zaproponuj funkcje realizowane przez system}

\begin{itemize}
  \item Rejestracja nowego użytkownika (firmy, osoby prywatnej)
  \item Edycja użytkownika
  \item Zmiana statusu użytkownika na usunięty
  \item Dodawanie osób do użytkownika
  \item Edytcja osób przydzielonych do użytkownika
  \item Zmiana statusu osoby na usunięty
  \item Wyświetlanie listy wszystkich wycieczek
  \item Wyświetlanie listy wszystkich aktywnych wycieczek z wolnymi miejscami
  \item Dodawanie wycieczki
  \item Edycja wycieczki
  \item Zmiana statusu wycieczki na: usuniętą, archiwalną, aktywną
  \item Dodawanie atrakcji do wycieczki
  \item Edycja atrakcji
  \item Zmiana statusu atrakcji na: usuniętą, archiwalną, aktywną
  \item Wyświetlanie listy wszystkich atrakcji przydzielonych do wycieczki
  \item Wyświetlanie listy aktywnych atrakcji przdzielonych do wycieczki
  \item Wyświetlanie listy krajów
  \item Dodawanie kraju
  \item Edycja kraju
  \item Usuwanie kraju nie przydzielonego do żadnej wycieczki
  \item Filtrowanie wycieczek po kraju
  \item Rezerwowanie wycieczki
  \item Dodawanie atrakcji do rezerwacji wycieczki
  \item Anulowanie rezerwacji wycieczki
  \item Wycofywanie rezerwacji atrakcji w wycieczce
  \item Dodawanie osób do rezerwacji wycieczki
  \item Edycja rezerwacji wycieczki
  \item Usuwanie osób z rezerwacji wycieczki
  \item Wprowadzanie wpłaty za wycieczkę
  \item Zmiany statusu rezerwacji na: anulowana, nie zapłacona, zapłacona częsciowo, zapłacona
\end{itemize}

\section{Zaprojektuj strukturę bazy danych}

\begin{itemize}

  \item trips - tabela przechowuje informacje na temat każdej wycieczki (ilośc wszystkich miejsc, data, status, nazwa, opis)
  \item attractions - tabela przechowuje informacje na temat każdej atrakcji powiązanej z wycieczką
  \item persons - tabela przechowuje tylko i wylacznie dane osobowe użytkownika lub osoby powiązanej z kontem użytkownika
  \item users - tabela użytkowników, w przypadku logowania do systemu przez web jak system uprawnień powinienna zostać użyta logika rozmyta
  \item company - tabela przechowuje informacje o firmach, powiazanych z mi. z użytkownikami
  \item reservations - głowna tabela rezerwacji
  \item reservation attractions - tabela przechowująca informacje jakie atrakcje zostały zarezerowane
  \item reservations prices - tabela przechowuje ifnormacje o cenach w jakich zostały zarezerwowane wycieczki
  \item reservation payment - tabela informuje o kwocie jaka juz została wpłacona przez rezerwującego
  \item reservation persons - tabela zawiera listę osób dla jakich jest rezerwowana dana wycieczka
  \item countrys - tabela z listą wszystkich krajów

\end{itemize}

\begin{figure}[ht]
 \centering
 \includegraphics[width=6in]{ERD.png}
 % ERD.png: 1178x699 pixel, 72dpi, 41.56x24.66 cm, bb=0 0 1178 699
 \caption{ERD}
\end{figure}

\subsection{Czyszczenie bazy}
\begin{verbatim}
 IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'reservation_attractions') 
DROP TABLE reservation_attractions; 
GO

--
-- Dropping table attractions : 
--

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'attractions') 
DROP TABLE attractions; 
GO

--
-- Dropping table reservations_prices : 
--

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'reservations_prices') 
Drop table reservations_prices ;
GO
--
-- Dropping table reservation_payment : 
--

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'reservation_payment') 
DROP TABLE reservation_payment; 
GO

--
-- Dropping table reservation_persons : 
--

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'reservation_persons') 
DROP TABLE reservation_persons; 
GO

--
-- Dropping table reservations : 
--
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'reservations') 
DROP TABLE reservations; 
GO

--
-- Dropping table trips : 
--

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'trips') 
DROP TABLE trips; 
GO

--
-- Dropping table persons : 
--

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'persons')
ALTER TABLE users DROP users_fk
DROP TABLE persons;
GO

--
-- Dropping table users : 
--

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users')
ALTER TABLE company DROP company_fk 
DROP TABLE users;
GO

--
-- Dropping table company : 
--

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'company') 
DROP TABLE company; 
GO


--
-- Dropping table countrys : 
--

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'countrys') 
DROP TABLE countrys;
GO

--
-- Dropping view countrys : 
--

IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'reservation_list') 
DROP VIEW reservation_list;
GO

\end{verbatim}

\subsection{Definicja tabel}

\begin{verbatim}

--
-- Definition for table countrys : 
--


CREATE TABLE [dbo].[countrys] (
  [id] int identity(1,1) PRIMARY KEY,
  [name] varchar(50) COLLATE Polish_CI_AS NOT NULL UNIQUE,
  [description] varchar(max) COLLATE Polish_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table trips : 
--

CREATE TABLE [dbo].[trips] (
  [id] int identity(1,1) PRIMARY KEY,
  [name] varchar(70) COLLATE Polish_CI_AS NOT NULL,
  [id_country] int NOT NULL,
  [price] money NOT NULL,
  [places_count] int NOT NULL,
  [dead_line] int NOT NULL,
  [start_date] datetime NOT NULL,
  [end_date] datetime NOT NULL,
  [description] varchar(max) COLLATE Polish_CI_AS NULL,
  [status] int NOT NULL DEFAULT 0 -- 0 - aktywna , 1 - usunieta , 2 - archiwum
)
ON [PRIMARY]
GO

--
-- Definition for table attractions : 
--

CREATE TABLE [dbo].[attractions] (
  [id] int identity(1,1) PRIMARY KEY,
  [id_trip] int NOT NULL,
  [name] varchar(70) COLLATE Polish_CI_AS NOT NULL,
  [description] varchar(max) COLLATE Polish_CI_AS NULL,
  [price] money NOT NULL,
  [places_count] int NOT NULL,
  [status] int NOT NULL DEFAULT 0 -- 0 - aktywna , 1 - usunieta , 2 - archiwum
)
ON [PRIMARY]
GO

--
-- Definition for table persons : 
--

CREATE TABLE [dbo].[persons] (
  [id] int identity(1,1) PRIMARY KEY,
  [id_user] int NULL,
  [name] varchar(50) COLLATE Polish_CI_AS NOT NULL,
  [last_name] varchar(50) COLLATE Polish_CI_AS NOT NULL,
  [street] varchar(80) COLLATE Polish_CI_AS NULL,
  [postal_code] varchar(20) COLLATE Polish_CI_AS NULL,
  [city] varchar(50) COLLATE Polish_CI_AS NULL,
  [building_number] varchar(10) COLLATE Polish_CI_AS NULL,
  [flat_number] varchar(10) COLLATE Polish_CI_AS NULL,
  [phone] varchar(50) COLLATE Polish_CI_AS NULL,
)
ON [PRIMARY]
GO

--
-- Definition for table users : 
--

CREATE TABLE [dbo].[users] (
  [id] int identity(1,1) PRIMARY KEY,
  [id_person] int NULL,
  [id_company] int NULL,
  [login] varchar(50) COLLATE Polish_CI_AS NOT NULL,
  [password] varchar(32) COLLATE Polish_CI_AS NOT NULL,
  [rights] varchar(50) COLLATE Polish_CI_AS NOT NULL,
  [email] varchar(max) COLLATE Polish_CI_AS NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table company : 
--

CREATE TABLE [dbo].[company] (
  [id] int identity(1,1) PRIMARY KEY,
  [id_user] int NULL,
  [name] varchar(150) COLLATE Polish_CI_AS NULL,
  [street] varchar(80) COLLATE Polish_CI_AS NULL,
  [postal_code] varchar(20) COLLATE Polish_CI_AS NULL,
  [city] varchar(50) COLLATE Polish_CI_AS NULL,
  [building_number] varchar(10) COLLATE Polish_CI_AS NULL,
  [flat_number] varchar(10) COLLATE Polish_CI_AS NULL,
  [phone] varchar(50) COLLATE Polish_CI_AS NULL
)
ON [PRIMARY]
GO

--
-- Definition for table reservations : 
--

CREATE TABLE [dbo].[reservations] (
  [id] int identity(1,1) PRIMARY KEY,
  [id_trip] int NOT NULL,
  [id_person] int NULL,
  [id_company] int NULL,
  [price] money NOT NULL,
  [reservation_date] datetime NOT NULL,
  [cancel_date] datetime NULL,
  [persons_count] int NOT NULL,
  [status] int NOT NULL  DEFAULT 0 -- 0 - normal, 1 - zaliczka, 2 - kolejna wplata, 3 - wykupiona, 4 - anulowana
)
ON [PRIMARY]
GO

--
-- Definition for table reservation_attractions : 
--

CREATE TABLE [dbo].[reservation_attractions] (
  [id] int identity(1,1) PRIMARY KEY,
  [id_reservation] int NOT NULL,
  [id_attraction] int NOT NULL,
  [places_count] int NOT NULL,
  [price] money NOT NULL,
-- [status] int NOT NULL,
  [reservation_date] datetime NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table reservation_prices: 
--
CREATE TABLE [dbo].[reservations_prices] (
  [id] int identity(1,1) PRIMARY KEY,
  [id_reservation] int NOT NULL,
  [places_count] int NOT NULL,
  [unplaces_count] int NOT NULL,
  [price] money NOT NULL,
  [reservation_date] datetime NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table reservation_payment : 
--

CREATE TABLE [dbo].[reservation_payment] (
  [id] int identity(1,1) PRIMARY KEY,
  [id_reservation] int NOT NULL,
  [payment] money NOT NULL,
  [payment_date] datetime NOT NULL
)
ON [PRIMARY]
GO

--
-- Definition for table reservation_persons : 
--

CREATE TABLE [dbo].[reservation_persons] (
  [id] int identity(1,1) PRIMARY KEY,
  [id_reservation] int NOT NULL,
  [id_person] int NOT NULL,
  [reservation_date] datetime NOT NULL
)
ON [PRIMARY]
GO

\end{verbatim}



\section{Zdefiniuj warunki integralności}

\subsection{Klucze obce}
\begin{verbatim}
--
-- Warunki Integralności - klucze obce
--

ALTER TABLE [dbo].[trips] 
ADD CONSTRAINT [trips_fk] FOREIGN KEY ([id_country])
  REFERENCES [dbo].[countrys] ([id])
  ON UPDATE NO ACTION 
GO

ALTER TABLE [dbo].[attractions] 
ADD CONSTRAINT [attractions_fk] FOREIGN KEY ([id_trip])
  REFERENCES [dbo].[trips] ([id])
  ON UPDATE CASCADE 
  ON DELETE CASCADE
GO

ALTER TABLE [dbo].[persons]
ADD CONSTRAINT [persons_fk] FOREIGN KEY ([id_user]) 
  REFERENCES [dbo].[users] ([id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[users] 
ADD CONSTRAINT [users_fk] FOREIGN KEY ([id_person]) 
  REFERENCES [dbo].[persons] ([id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[users] 
ADD CONSTRAINT [users_fk2] FOREIGN KEY ([id_company]) 
  REFERENCES [dbo].[company] ([id]) 
  ON UPDATE SET DEFAULT
  ON DELETE SET DEFAULT
GO


ALTER TABLE [dbo].[company] 
ADD CONSTRAINT [company_fk] FOREIGN KEY ([id_user]) 
  REFERENCES [dbo].[users] ([id]) 
  ON UPDATE NO ACTION
  ON DELETE NO ACTION
GO

ALTER TABLE [dbo].[reservations]
ADD CONSTRAINT [reservations_fk] FOREIGN KEY ([id_trip]) 
  REFERENCES [dbo].[trips] ([id]) 
  ON UPDATE CASCADE
  ON DELETE CASCADE
GO


ALTER TABLE [dbo].[reservations]
ADD CONSTRAINT [reservations_fk3] FOREIGN KEY ([id_company]) 
  REFERENCES [dbo].[company] ([id]) 
GO


ALTER TABLE [dbo].[reservation_attractions]
ADD CONSTRAINT [reservation_attractions_fk] FOREIGN KEY ([id_reservation]) 
  REFERENCES [dbo].[reservations] ([id]) 
  ON UPDATE CASCADE
  ON DELETE CASCADE
GO

ALTER TABLE [dbo].[reservation_attractions]
ADD CONSTRAINT [reservation_attractions_fk2] FOREIGN KEY ([id_attraction]) 
  REFERENCES [dbo].[attractions] ([id]) 
GO

ALTER TABLE [dbo].[reservation_payment]
ADD CONSTRAINT [reservation_payment_fk] FOREIGN KEY ([id_reservation]) 
  REFERENCES [dbo].[reservations] ([id]) 
    ON UPDATE CASCADE
	ON DELETE CASCADE
GO

ALTER TABLE [dbo].[reservation_persons]
ADD CONSTRAINT [reservation_persons_fk] FOREIGN KEY ([id_reservation]) 
  REFERENCES [dbo].[reservations] ([id]) 
  ON UPDATE CASCADE
  ON DELETE CASCADE
GO

ALTER TABLE [dbo].[reservation_persons]
ADD CONSTRAINT [reservation_persons_fk2] FOREIGN KEY ([id_person]) 
  REFERENCES [dbo].[persons] ([id]) 
GO

ALTER TABLE [dbo].[reservations_prices]
ADD CONSTRAINT [reservation_prices_fk2] FOREIGN KEY ([id_reservation]) 
  REFERENCES [dbo].[reservations] ([id]) 
  ON UPDATE CASCADE
  ON DELETE CASCADE
GO

\end{verbatim}

\subsection{Checki}

\begin{verbatim}
 
ALTER TABLE [dbo].[trips]
    ADD CONSTRAINT min_price CHECK ( price > 0 )
GO

ALTER TABLE [dbo].[trips]
    ADD CONSTRAINT min_places_count CHECK ( places_count > 0 )
GO

ALTER TABLE [dbo].[trips]
    ADD CONSTRAINT min_dead_line CHECK ( dead_line > 0 )
GO

ALTER TABLE [dbo].[trips]
    ADD CONSTRAINT min_date CHECK ( start_date > GETDATE() AND end_date > start_date   )
GO


ALTER TABLE [dbo].[attractions]
    ADD CONSTRAINT min_price_attractions CHECK ( price > 0  )
GO

ALTER TABLE [dbo].[attractions]
    ADD CONSTRAINT attractions_places_count CHECK ( places_count > 0  )
GO

ALTER TABLE [dbo].[reservations]
    ADD CONSTRAINT min_date_reservations CHECK ( reservation_date >= GETDATE() OR ( cancel_date is null OR cancel_date >= reservation_date )  )
GO

ALTER TABLE [dbo].[reservation_attractions]
    ADD CONSTRAINT min_price_reservation_attractions CHECK ( price > 0  )
GO

ALTER TABLE [dbo].[reservation_payment]
    ADD CONSTRAINT min_price_reservation_payment CHECK ( payment > 0  )
GO

-- checki związane z polami statusu

ALTER TABLE [dbo].[trips] 
	ADD CONSTRAINT trips_status_check CHECK ( status in ( 0 , 1 , 2 ) )
GO

ALTER TABLE [dbo].[attractions] 
	ADD CONSTRAINT attractions_status_check CHECK ( status in ( 0 , 1 , 2 ) )
GO

ALTER TABLE [dbo].[reservations] 
	ADD CONSTRAINT reservations_status_check CHECK ( status in ( 0 , 1 , 2 ,3 , 4) )
GO
\end{verbatim}



\section{Wygeneruj dane testowe}

\subsection{Generowane za pomocą INSERT INTO}

\begin{verbatim}
 --
-- Dane testowe
--

INSERT INTO [projekt].[dbo].[countrys] ([name],[description])
     VALUES ( 'Polska', 'kraj miodem i mlekiem płynący'),
			( 'Niemcy', 'kraj pięknych kobiet'),
			( 'Czechy', 'kraj piwem płynący'),
			( 'Rosja',  'kraj niedźwiedziami płynący'),
			( 'Austria', 'kraj jak kraj')
GO

INSERT INTO [projekt].[dbo].[trips]
           ([name]
           ,[id_country]
           ,[price]
           ,[places_count]
           ,[dead_line]
           ,[start_date]
           ,[end_date]
           ,[description]
           ,[status])
     VALUES
           ('Ciekawa',1,500,15,45,'2010-02-01','2010-02-10','Bardzo ciekawa wycieczka',0),
           ('Mniej Ciekawa',2,1000,30,45,'2010-02-01','2010-02-10','Bardzo ciekawa wycieczka',0),
           ('Bardziej Ciekawa',3,1000,45,45,'2010-02-05','2010-02-15','Bardzo ciekawa wycieczka',0),
           ('Tak Samo Ciekawa',4,1500,45,45,'2010-03-01','2010-03-10','Bardzo ciekawa wycieczka',0),
           ('Wogole nie Ciekawa',5,2000,20,45,'2010-03-01','2010-03-10','Bardzo ciekawa wycieczka',0)
           
GO

INSERT INTO [projekt].[dbo].[attractions]
           ([id_trip]
           ,[name]
           ,[description]
           ,[price]
           ,[places_count]
           ,[status])
     VALUES
           (1,'Narty', 'Zjazd ze schodów',50,50,0),
           (1,'Kulig', 'Też zjazd ze schodów',150,50,0),
           (1,'Zwiedzanie doliny siedmiu stawów', 'Oj tak',250,50,0),
           (1,'Paint Ball', 'w schowku na zmiotki',350,50,0),
           
		   (2,'Zwiedzanie Berlina', '',50,50,0),
           (2,'Zwiedzanie fabryki golfa', '',50,50,0),
           (2,'Jazda konno', ' ',50,50,0),
           (2,'Brakuje mi pomyslow', '',50,50,0),
           
           (3,'Picie piwa', 'wraz z zjazdem ze schodów',50,50,0),
           (3,'Nic', 'wieczorek taneczno bokserski',50,50,0),
           (3,'Cos', 'bla bla',50,50,0),
           (3,'Narty', '1',50,50,0),
           
           (4,'44', '44',50,50,0),
           (4,'55', '55',50,50,0),
           
           (5,'44', '55',50,50,0),
           (5,'55', '55',50,50,0)
GO

INSERT INTO [projekt].[dbo].[company]
           ([id_user]
           ,[name]
           ,[street]
           ,[postal_code]
           ,[city]
           ,[building_number]
           ,[flat_number]
           ,[phone])
     VALUES
           ( null , 'Nazwa' ,'Ulica'	,'Kod pocztowy' , 'Miasto'		,'Nr. bu' ,'Nr. mie' ,'telefon' ),
           ( null , 'Firma' ,'Krakowska', '69-666'		, 'Kraków'		, '2','3'			 ,'543'),
           ( null , 'ASCII' ,'Tarnowska', '33-100'		, 'Tarnów'		, '2','3'			 ,'345345'),
           ( null , 'HEX'	,'Teksańska', ''			, 'Teksas'		, '2','3'			 ,'90987789'),
           ( null , 'DEC'	,'Mechów'	, '33-123'		, 'Rydzykowska'	, '2','3'			 ,'34534534')
GO


INSERT INTO [projekt].[dbo].[persons]
           ([id_user]
           ,[name]
           ,[last_name]
           ,[street]
           ,[postal_code]
           ,[city]
           ,[building_number]
           ,[flat_number]
           ,[phone])
     VALUES
           (null	,'Piotr'	,'Wilk'		,'Fąfary'	,'33-100'	,'Tarnów'	,'1'	,'2'	,'544689'),
           (null	,'Piotr'	,'Zegar'	,'Torowa'	,'33-100'	,'Tarnów'	,'2'	,'3'	,'123123'),
           (null	,'Kasia'	,'Nowak'	,'Krakowska','33-123'	,'Kraków'	,'3'	,'4'	,'544689'),
           (null	,'Zbychu'	,'Zboczny'	,'Zboku'	,'33-123'	,'Piekło'	,'4'	,'5'	,'544689')
           
GO

INSERT INTO [projekt].[dbo].[users]
           ([id_person]
           ,[id_company]
           ,[login]
           ,[password]
           ,[rights]
           ,[email])
     VALUES
           (1,1,'login','haslo','','zefee@op.pl'),
           (2,2,'piotr','piotr','','rtoip@op.pl'),
           (3,3,'tim','mit','','timit@ojess.pl'),
           (4,4,'armstrong','strong','','rmong@pol.pl')
GO
\end{verbatim}

\subsection{Generowane za pomocą procedur}

\begin{verbatim}

 --@trip_id int, @person_id int, @company_id int,@persons_count int
addReservations 1,1,null,5
GO
addReservations 2,1,1,10
GO
addReservations 2,2,null, 15
GO
addReservations 3,2,null,20
GO

cancelReservations 1
GO
--@reservation_id int, @attractions_id int, @person_count int
addAtractionToReservation 2,5,10
GO
addAtractionToReservation 2,6,10
GO

--addPaymentToReservation 1,50

Go

\end{verbatim}


\section{Zaproponuj i zdefiniuj strukturę indeksów}

\begin{verbatim}
 --
-- Indeksy
--

--CREATE UNIQUE INDEX [HASH_countrys] ON [dbo].[countrys]
--  ([name])
--USING HASH 
--GO

CREATE INDEX [IP_trip] ON [dbo].[trips]
  ([price]) 
GO

CREATE INDEX [ID_trip] ON [dbo].[trips]
  ([start_date], [end_date]) 
GO

CREATE INDEX [ID_reservation] ON [dbo].[reservations]
  ([reservation_date] ) 
GO

CREATE INDEX [ID_reservation] ON [dbo].[attractions]
  ([price] ) 
GO

\end{verbatim}


\section{Zaproponuj i zdefiniuj strukturę widoków (view) ułatwiającą dostęp do danych}

\begin{verbatim}
 --
-- Widoki
--

-- Lista rezerwacji + mozna dodac jeszcze cene_all i cene zaplacona za wszystko
-- czyli plus atrakcje


CREATE VIEW reservation_list AS 
SELECT  reservations.persons_count, reservations.reservation_date, reservations.status,
		trips.price, trips.start_date, trips.end_date, trips.places_count,
		countrys.name as country_name, countrys.description,
		company.name as company_name
FROM reservations,trips,countrys,company
WHERE reservations.id_trip = trips.id AND trips.id_country = countrys.id AND 
reservations.id_company = company.id

\end{verbatim}

\section{Zaproponuj i zdefiniuj zestaw operacji na danych (procedury składowane, trigery itp.)}

\begin{verbatim}
 --
-- Procedury trigery
--
Go


IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'addReservations' AND type = 'P')
	DROP PROCEDURE addReservations
GO
					

CREATE PROCEDURE addReservations
@trip_id int,
@person_id int,
@company_id int,
@persons_count int
AS

DECLARE @price int 
DECLARE @NEWID int
--pytanie co z cena ?bo nie ma w tabeli reserwacje
--i czy przenosimy preson_count do innej tabeli

-- potrzebujemy cene z glownej tabeli
SELECT @price = price FROM trips WHERE id = @trip_id;
SET NOCOUNT ON 
INSERT INTO reservations VALUES(@trip_id, @person_id, @company_id, 0, GETDATE(),NULL,@persons_count, 0)
SELECT @NEWID = SCOPE_IDENTITY()

--insert the price and number of people to reservation_prices
INSERT INTO [projekt].[dbo].[reservations_prices]
           ([id_reservation]
           ,[places_count]
           ,[unplaces_count]
           ,[price]
           ,[reservation_date])
     VALUES (@NEWID, @persons_count, 0 ,@price, GETDATE() )
GO

IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'cancelReservations' AND type = 'P')
	DROP PROCEDURE cancelReservations
GO

CREATE PROCEDURE cancelReservations
@reservations_id int
AS
UPDATE reservations SET cancel_date=GETDATE(),status=1 WHERE id = @reservations_id

GO


IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'addAtractionToReservation' AND type = 'P')
	DROP PROCEDURE addAtractionToReservation
GO

CREATE PROCEDURE addAtractionToReservation
@reservation_id int,
@attractions_id int,
@person_count int
AS
DECLARE @price int 
DECLARE @NEWID int

SELECT @price = price FROM attractions WHERE id = @attractions_id;
SET NOCOUNT ON 
INSERT INTO reservation_attractions VALUES(@reservation_id, @attractions_id, @person_count, @price, GETDATE())
SELECT @NEWID = SCOPE_IDENTITY()

Go

IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'addPaymentToReservation' AND type = 'P')
	DROP PROCEDURE addPaymentToReservation
GO

CREATE PROCEDURE addPaymentToReservation
@reservation_id int,
@payment money
AS
DECLARE @NEWID int
SET NOCOUNT ON 
INSERT INTO reservation_payment VALUES( @reservation_id, @payment, GETDATE())
SELECT @NEWID = SCOPE_IDENTITY()

INSERT INTO [projekt].[dbo].[reservation_payment]
           ([id_reservation]
           ,[payment]
           ,[payment_date])
     VALUES
           (@reservation_id ,@payment ,GETDATE())
GO

IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'addPersonsToReservation' AND type = 'P')
	DROP PROCEDURE addPersonsToReservation
GO

CREATE PROCEDURE addPersonsToReservation
@reservation_id int,
@person_id int
AS
INSERT INTO [projekt].[dbo].[reservation_persons]
           ([id_reservation]
           ,[id_person]
           ,[reservation_date])
     VALUES
           (@reservation_id,@person_id,GETDATE())
GO


\end{verbatim}


\section{Zaproponuj i zdefiniuj strukturę uprawnień do danych}

\begin{verbatim}
 --
-- Struktura uprawnień
--

--
--	Deklaracja zwyklego uzytkownika 
--

CREATE LOGIN normal_user WITH PASSWORD = 'pa$$word';
CREATE USER normal_user FOR LOGIN normal_user;

GRANT SELECT ON attractions TO normal_user 
GRANT SELECT ON company TO normal_user
GRANT SELECT ON countrys TO normal_user
GRANT SELECT, INSERT, UPDATE, DELETE ON persons TO normal_user
GRANT SELECT ON reservation_attractions TO normal_user
GRANT SELECT ON reservation_payment TO normal_user
GRANT SELECT, INSERT ON reservation_persons TO normal_user
GRANT SELECT ON reservations_prices TO normal_user
GRANT SELECT ON reservations TO normal_user
GRANT SELECT ON trips TO normal_user
GRANT SELECT ON users TO normal_user

--
--	Deklaracja admina  
--

CREATE LOGIN admin_user WITH PASSWORD = 'pa$$word';
USE projekt;
CREATE USER admin_user FOR LOGIN admin_user;

GRANT SELECT, INSERT, UPDATE, DELETE ON attractions TO admin_user 
GRANT SELECT, INSERT, UPDATE, DELETE ON company TO admin_user
GRANT SELECT, INSERT, UPDATE, DELETE ON countrys TO admin_user
GRANT SELECT, INSERT, UPDATE, DELETE ON persons TO admin_user
GRANT SELECT, INSERT, UPDATE, DELETE ON reservation_attractions TO admin_user
GRANT SELECT, INSERT, UPDATE, DELETE ON reservation_payment TO admin_user
GRANT SELECT, INSERT, UPDATE, DELETE ON reservation_persons TO admin_user
GRANT SELECT, INSERT, UPDATE, DELETE ON reservations_prices TO admin_user
GRANT SELECT, INSERT, UPDATE, DELETE ON reservations TO admin_user
GRANT SELECT, INSERT, UPDATE, DELETE ON trips TO admin_user
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO admin_user

GO
\end{verbatim}



\end{document}

